package com.augurit.gzcgwsp.csshlj.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Session;
import org.jbpm.api.ProcessEngine;
import org.jbpm.api.task.Task;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;
import com.augurit.common.system.web.auth.form.LoginUserForm;
import com.augurit.common.util.HqlUtils;
import com.augurit.common.util.PageUtils;
import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.csshlj.convert.YwHwShljysConvertor;
import com.augurit.gzcgwsp.csshlj.dao.ShljYsDao;
import com.augurit.gzcgwsp.csshlj.entity.ShljYs;
import com.augurit.gzcgwsp.csshlj.form.ShljYsBaForm;
import com.augurit.gzcgwsp.csshlj.form.ShljYsForm;
import com.augurit.gzcgwsp.csshlj.service.ICsshljCzService;
import com.augurit.gzcgwsp.csshlj.service.IShljYsService;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.excelimportexport.util.FileUtil;
import com.augurit.gzcgwsp.excelimportexport.util.GetFileName;
import com.augurit.gzcgwsp.printDoc.entity.YwPrintDoc;
import com.augurit.gzcgwsp.sendNotice.service.IExchangeStateService;
import com.augurit.gzcgwsp.sendNotice.service.IYwMtbjlshInfoService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceForWbService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceInfoService;
import com.augurit.gzcgwsp.smartformCsdlwj.common.SmartDao;
import com.augurit.gzcgwsp.smartformCsdlwj.entity.YwHwYsclgj;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwCltfcsqkService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwLyqsclqksmService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwXminfoService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwYsclgjService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwYszzService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwZxqksmService;
import com.augurit.gzcgwsp.smartformCsdlwj.web.form.ShljqsForm;
import com.augurit.gzcgwsp.wfex.common.service.IWfServiceEX;
import com.augurit.gzcgwsp.ywcmn.entity.YwComSjcllist;
import com.augurit.gzcgwsp.ywcmn.service.IYwComPrintService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjclService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjcllistService;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComQyxxForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjclForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjcllistForm;
import com.augurit.smartform.service.IWfSupportService;
import com.augurit.wf.bi.convert.WfBusInstanceConvertor;
import com.augurit.wf.bi.service.IWfBusInstanceService;
import com.augurit.wf.bi.web.form.WfBusInstanceForm;
import com.augurit.wf.bi.web.form.WfBusInstanceTaskForm;
import com.augurit.wf.common.constant.WfConstant;
import com.augurit.wf.common.entity.WfBusInstance;
import com.augurit.wf.common.service.IWfService;
import com.augurit.wf.common.service.impl.WfBusClientServiceImpl;

/**
 * 目前这个类，只有与方欣的数据交换用到
 * @author Administrator
 *
 */

@Service
@Transactional
public class ShljYsServiceImpl extends WfBusClientServiceImpl<ShljYsForm> implements IShljYsService{

	private static final Logger log=Logger.getLogger(ShljYsServiceImpl.class);

	@Autowired
	private ShljYsDao shljYsDao;
    @Autowired
    private IYwComQyxxService ywComQyxxService;
    @Autowired
	private IYwComSjcllistService ywComSjcllistService;
	@Autowired
	private IYwComSjclService ywComSjclService;	
	@Autowired
    private IYwComPrintService ywComPrintService;
	@Autowired
	private ICsshljCzService csshljCzService;
	@Autowired
    private IWfService wfService;
	@Autowired
    private IWfServiceEX wfServiceEX;
	@Autowired
    private ProcessEngine processEngine;	
	@Autowired
	private IYwHwLyqsclqksmService ywHwLyqsclqksmService;
	@Autowired
	private IYwHwCltfcsqkService ywHwCltfcsqkService;
	@Autowired
	private IYwHwYszzService ywHwYszzService;
	@Autowired
	private IYwHwZxqksmService ywHwZxqksmService;	
	@Autowired
	private IYwHwYsclgjService ywHwYsclgjService;
	@Autowired
	private SmartDao smartDao;
	@Autowired
	private IWfBusInstanceService wfBusInstanceService;
	@Autowired
	private IWfSupportService wfSupportService;
	@Autowired
	private IYwHwXminfoService ywHwXminfoService;
	@Autowired
    private IYwMtbjlshInfoService ywMtbjlshInfoService;
    @Autowired
	private IYwWebserviceInfoService ywWebserviceInfoService;
    @Autowired
    private IYwWebserviceForWbService ywWebserviceForWbService;
    @Autowired
    private IExchangeStateService exchangeStateService;
	/**
	 * ��������ȡForm����
	 */
	@Transactional(readOnly = true)
	public ShljYsForm get(Long id) {
		ShljYs entity = shljYsDao.get(id);
		ShljYsForm form = YwHwShljysConvertor.convertVoToForm(entity);
        //YwComQyxxForm qyxxform = ywComQyxxService.get(Long.parseLong(form.getQybh()));
        //YwSrDlzyConvertor.convertQyxxFormToForm(qyxxform, form);
        //form.setSqdw(qyxxform.getSqdw());
        return form;
	}
	
	//通过案件号获得实体类
    public List<ShljYs> getByAcceptNo(String acceptNo) {
    	String sql = " from ShljYs t where t.acceptNo='" + acceptNo + "'";
        List<ShljYs> entityList = shljYsDao.find(sql);
        return entityList;
    }
    

    public void changeYslState(Long id,Long state,String yslThyy) {
    	StringBuffer sql = new StringBuffer();
    	sql.append("update ShljYs t set t.ysl='"+state+"' ");
    	if(StringUtils.isNotBlank(yslThyy)){
    		sql.append(" ,t.yslThyy='"+yslThyy+"'");
    	}
    	sql.append(" where t.id="+id);
    	shljYsDao.getSession().createQuery(sql.toString()).executeUpdate();
    	System.out.println("水域收件");
    }
    
    //是否延期办理
    public void changeIsycbl(Long id, String state) {
    	ShljYs entity = shljYsDao.get(id);
//    	entity.setIsycbl(state);
    	shljYsDao.save(entity);
	}
    

    //保存结案时间
	public void endSave(Long id) {
		ShljYs entity = shljYsDao.get(id);
    	entity.setJarq(new Date());
    	shljYsDao.save(entity);
	}
    
	public void delete(Long... ids) {
		shljYsDao.delete(ids);
	}

	/**
	 * �����������޸ĵ�Form�����б�
	 */
	public void save(ShljYsForm... forms) {
		if(forms != null)
			for(ShljYsForm form : forms)
				this.save(form);
	}
	
	/**
	 * �����������޸ĵ�Form����
	 */
	public void save(ShljYsForm form){
		System.out.println("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&"+form.getQymc());
		
        if (form == null) return;

        ShljYs entity = new ShljYs();
        
      //保存企业信息
        YwComQyxxForm qyxxForm = new YwComQyxxForm();
        if(StringUtils.isEmpty(form.getQybh())){
        	if(StringUtils.isNotBlank(form.getSqdw())){
            	YwComQyxxForm ywComQyxxForm = ywComQyxxService.getFormBySqdw(form.getSqdw(), "shljys");
            	if(ywComQyxxForm != null){
            		qyxxForm = ywComQyxxForm;
            	}
            }
        }
        YwHwShljysConvertor.convertFormToQyxxForm(form, qyxxForm);
        qyxxForm.setTemplateCode("shljys");
        ywComQyxxService.update(qyxxForm);
        form.setQybh(qyxxForm.getId().toString());
        System.out.println("&&&&&&&&&&&&&&&&&&&&&  qyxxForm =  "+qyxxForm.getId());

        
        boolean isNewForm = false;
        if (StringUtils.isBlank(form.getAcceptNo())) {
        	//生成政务中心统一查询编号 开始
			Map<String,String> serialNoAndPasswordMap = null;
        	if("Y".equals(form.getSendNotice())){//网办，1
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("1");
        	}else{//大厅，0
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("0");
        	}
        	String serialNo = serialNoAndPasswordMap.get("serialNo");
        	String password = serialNoAndPasswordMap.get("password");
			form.setSerialNo(serialNo);
			form.setPassword(password);
			form.setAcceptNo(serialNo);
    		//生成政务中心统一查询编号 结束
            isNewForm = true;
        }
        YwHwShljysConvertor.convertFormToVo(form, entity);
        System.out.println("&&&&&&&&&&&&&&&&&&&&&  后  "+entity.getQymc());
        System.out.println("&&&&&&&&&&&&&&&&&&&&&  entity.getId() =  "+entity.getId());
        Session session = shljYsDao.getSessionFactory().openSession();
        session.save(entity);
        session.flush();
        session.close();
        form.setId(entity.getId());
        
//        if (isNewForm) {
//            sjclService.updateByNewForm(form.getId());
//        }
	}

	/**
	 * ���Form����Ĳ�ѯ��������ҳ��ѯ
	 */
	@Transactional(readOnly = true)
	public Page<ShljYsForm> search(Page<ShljYsForm> page, ShljYsForm form) {
		// �����µķ�ҳ����
		Page pg = PageUtils.newPageInstance(page);
		
		// ��ѯ��估����
		StringBuffer hql = new StringBuffer("from ShljYs ps where 1=1");
		List values = new ArrayList();
		
		// ��ѯ����
		if(form != null){
			if(form.getQymc()!=null){
				 hql.append(" and ps.qymc LIKE '%"+form.getQymc()+"%'");				
			}
			 hql.append(" and ps.fzh is not NULL ");
		}
		
		//����
		hql.append(HqlUtils.buildOrderBy(page, "ps"));
		
		// ִ�з�ҳ��ѯ����
		shljYsDao.findPage(pg, hql.toString(), values);
		
		// ת��ΪForm�����б?��ֵ��ԭ��ҳ������
		List<ShljYsForm> list = YwHwShljysConvertor.convertVoListToFormList(pg.getResult());
		PageUtils.copy(pg, list, page);
		return page;
	}
	
	/**
	 * ��ݹ��������б�����ҳ��ѯ
	 */
	@Transactional(readOnly = true)
	public Page<ShljYsForm> search(Page<ShljYsForm> page, List<PropertyFilter> filters) {
		// �����µķ�ҳ����
		Page<ShljYs> pg = PageUtils.newPageInstance(page);
		
		// ������������ҳ���Ҷ���
		///ywSrDlzyDao.findPage(pg, filters);
		
		// ת��ΪForm�����б?��ֵ��ԭ��ҳ������
		//List<DlzyForm> list = YwSrDlzyConvertor.convertVoListToFormList(pg.getResult());
		//PageUtils.copy(pg, list, page);
		return page;
	}
	
	/**
	 * 通过ID号,改变窗口是否可打印受理通知书状态(1为是；0和空为否)
	 * @param acceptNo 受理号
	 * @return Page<YwRqSsgdSqb>
	 */
	public void changeSendAcceptNotice(Long id){
		ShljYs entity = shljYsDao.get(id);
//		entity.setSendAcceptNotice("1");
		shljYsDao.save(entity);
	}
	
	/**
	 * 通过ID号,改变窗口是否可打印延期办理结果通知书状态(1为是；0和空为否)
	 * @param id 
	 * @return 
	 */
	public void changeSendYqNotice(Long id){
		ShljYs entity = shljYsDao.get(id);
//		entity.setSendYqNotice("1");
		shljYsDao.save(entity);
	}
	
	/**
	 * 根据ID获得对象实体
	 * @param id
	 * @return
	 */  
	public ShljYs getEntity(Long id){
		return shljYsDao.get(id);
	}
	
	/**
     * 保存对象实体
     * @param ywSrDlzy
     */     
    public void saveEntity(ShljYs shljYs){
    	shljYsDao.save(shljYs);
    }
    
    /*
     * 查询受理日期(non-Javadoc)
     * @see com.augurit.gzcgwsp.smartformCsdlwj.service.ISmartFormCsdlwjService#getSlrq(java.lang.Long, java.lang.String, java.lang.Long)
     */
	public String getSlrq(Long id, String fileCode, Long tempId) {
		String date="";
        List<YwPrintDoc> docs=this.ywComPrintService.findDoc(id,fileCode,tempId);
        if(docs!=null && docs.size()>0){
	            YwPrintDoc doc=docs.get(0);
	            Date createDate=doc.getCreatDate();
	            try {
					date=DateUtilsEX.convertDate2Chinese(createDate);
				} catch (ParseException e) {
					e.printStackTrace();
				}
        }
		return date;
	}
	
	public List getListById(Long id){
        String sql="select * from SYSHLJ_V1 where id="+id;
        return shljYsDao.getListById(sql);
    }

	public boolean checkData(String templateCode, String filename, Long id,
			Long qyid) {
		
		return this.csshljCzService.checkData(templateCode, filename, id, qyid);
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.IShljYsService#importData(com.augurit.gzcgwsp.csshlj.form.ShljYsForm, java.util.Map)
	 */
	public String importData(ShljYsForm form, Map map) {
		String result="";
		String contextpath=(String) map.get("contextPath");
		String templateCode=(String) map.get("templateCode");
		String ajid=(String) map.get("id");
		String qybh = (String) map.get("qybh");
	    
        //查询上传的材料
        String filepath=getSqbFilePath(map);
        filepath=(contextpath+filepath).replace("/", File.separator);
        File file=new File(filepath);
        if(StringUtils.isNotBlank(filepath)){
        	 Workbook wb;
        	  try {
				wb = Workbook.getWorkbook(file);
                  Sheet[] sheets=wb.getSheets();
                  int cartools=0;
                  int placesite=0;
                  int transportdevice=0;
                  int execute=0;
                  for(int k=3;k<sheets.length;k++){
                	  if(StringUtils.isNotBlank(sheets[k].getName())){
                		  if(sheets[k].getName().indexOf("运输车辆工具情况说明")>-1){
                			  cartools=k;
                		  }
                		 
                	  }
                	  if(StringUtils.isNotBlank(sheets[k].getName())){
                		  if(sheets[k].getName().indexOf("停放场所情况")>-1){
                			  placesite=k;
                		  }
                		 
                	  }
                	  if(StringUtils.isNotBlank(sheets[k].getName())){
                		  if(sheets[k].getName().indexOf("运输装置")>-1){
                			  transportdevice=k;
                		  }
                		 
                	  }
                	  if(StringUtils.isNotBlank(sheets[k].getName())){
                		  if(sheets[k].getName().indexOf("执行情况说明")>-1){
                			  execute=k;
                		  }
                	  }
                  }
                  result+=this.importInfoData(sheets[2], Long.parseLong(ajid));
                  InputStream is=new FileInputStream(filepath);
                  HSSFWorkbook hssfworkbook = new HSSFWorkbook(is);// 将is流实例到
                  //多个车辆情况说明的情况
                  for(int k=3;k<sheets.length;k++){
                	  if(StringUtils.isNotBlank(sheets[k].getName())){
                		  if(sheets[k].getName().indexOf("车辆情况说明")>-1){//可能出现多个车辆情况说明
                			  ywHwLyqsclqksmService.parseYwHwLyqsclqksm(hssfworkbook.getSheetAt(k), Long.parseLong(qybh), Long.parseLong(ajid),templateCode,k);//导入 生活垃圾清扫车辆（清捞船只）情况说明
                          }
                	  }
                  }
            	    ywHwYsclgjService.parseYwHwYsclgj(hssfworkbook.getSheetAt(cartools),  Long.parseLong(qybh), Long.parseLong(ajid), templateCode);
                    ywHwCltfcsqkService.parseYwHwCltfcsqk(hssfworkbook.getSheetAt(placesite),  Long.parseLong(qybh), Long.parseLong(ajid),templateCode);//导入 办公和车辆停放场所情况
                    ywHwYszzService.parseYwHwYszz(hssfworkbook.getSheetAt(transportdevice),  Long.parseLong(qybh), Long.parseLong(ajid),templateCode);
                    ywHwZxqksmService.parseYwHwZxqksm(hssfworkbook.getSheetAt(execute),  Long.parseLong(qybh), Long.parseLong(ajid), templateCode);
                    
                  HSSFRow hssfrow = hssfworkbook.getSheetAt(2).getRow(hssfworkbook.getSheetAt(2).getPhysicalNumberOfRows()-1);
                  if(hssfrow!=null){
                	  String ysgjjsxn;
                	  if (hssfrow.getCell((short) 0) == null) {
                		  ysgjjsxn = "";
                	  } else if (hssfrow.getCell((short) 0).getCellType() == 0) {
                		  ysgjjsxn = new Double(hssfrow.getCell((short) 0).getNumericCellValue()).toString();
                	  } else {// 如果EXCEL表格中的数据类型为字符串型
                		  ysgjjsxn = hssfrow.getCell((short) 0).getStringCellValue().trim();
                	  }
                	  if(!StringUtils.isEmpty(ysgjjsxn)){
                		  String sql = "Update SYSHLJ_V1 SET YSGJJSXN ='"
                			  + ysgjjsxn 
                			  + "' where ID = " + Long.parseLong(ajid);
                		  smartDao.executeSql(sql);
                	  }
                  }
	        } catch (Exception e) {
	        	e.printStackTrace();
				  result+="收件材料没有符合条件的文件\n";
		    }
        }else{
            result+="收件材料没有符合条件的文件\n";
            
        }
        
        filepath=this.getSqbFilePath(map);
        filepath=(contextpath+filepath).replace("/", File.separator);
        file=new File(filepath);
        if(StringUtils.isNotBlank(filepath)){
        	try{
            	InputStream is=new FileInputStream(filepath);
            	ywHwXminfoService.importExcel(is, Long.parseLong(qybh), Long.parseLong(ajid),templateCode);
        	} catch (Exception e) {
				  result+="收件材料没有符合条件的文件\n";
		    }
        }
        
		return null;
	}
	
	 /**
     * 功能:导入 处置服务行政许可申请书导入模板.xls 入库操作<br>
     * 参数:<br>
     * 返回:<br>
     * 作者:庄国坤<br>
     * 说明:<br>
     */
    public String importInfoData(Sheet sheet ,long ajid) throws Exception {
    	String rt="";
        try{
        //解释sheet
        int offsetRow = 0, offsetCol=-1,row = 0;//如果行列有变化,只需修改此偏移量即可.by zhgk 2012/12/17
        row=1+offsetRow;

        String qs = ExcelUtil.readData(sheet,1,0);
        String sj = ExcelUtil.readData(sheet, 2, 0);
        String ys = ExcelUtil.readData(sheet, 3, 0);
/*          String applyName = ExcelUtil.readData(sheet,1+offsetCol,
                row)
                + ",";
        applyName += ExcelUtil.readData(sheet, 2+offsetCol, row)
                + ",";
        applyName += ExcelUtil.readData(sheet, 3+offsetCol, row)
                + ",";
        if (applyName != null) {
            applyName = applyName.substring(0, applyName.length() - 1);
        }*/
        
        row++;
        String companyName = ExcelUtil.readData(sheet, offsetCol + 1,
                row);
        String orgCodeName = ExcelUtil.readData(sheet,3,
                1);

        row++;
        String lawPerson= ExcelUtil.readData(sheet, 1,
               2);
        
        row++;
        String bgzs  = ExcelUtil.readData(sheet,  1,
               4);//办公住所
        String regCode = ExcelUtil.readData(sheet, 3,
                2);
        
        row++;
        String telphone = ExcelUtil.readData(sheet,1,
               3);
        String faxCode = ExcelUtil.readData(sheet, offsetCol + 4,
                3); 
        
        row++;
        String lawPersonEmail = ExcelUtil.readData(sheet, offsetCol + 2,
               7);
        String postCode = ExcelUtil.readData(sheet, offsetCol + 4,
                7); 

        row++;
        String projectName = ExcelUtil.readData(sheet, offsetCol + 1,
                row); 
        
        row++;
        String regMoney = ExcelUtil.readData(sheet, offsetCol + 2,
               4); 
        

        row++;
        String agentMan = ExcelUtil.readData(sheet, offsetCol + 2,
               6); 

        row++;
        String agentManTel = ExcelUtil.readData(sheet, offsetCol + 2,
               9); 
        
        row++;
        String agentManFax = ExcelUtil.readData(sheet, offsetCol + 2,
               10);
        
        row++;
        String agentManEmail = ExcelUtil.readData(sheet, offsetCol + 2,
               11); 
        String remark = ExcelUtil.readData(sheet, 0,
                12); 
        ShljYs obj;
        List list =new ArrayList();
        list=getListById2(ajid,list);
        if(list!=null && list.size()>0){
      	  obj=(ShljYs) list.get(0);
        }else{

            obj = new ShljYs();
        }
//        obj.setSqnrqs(applyName);
        if(StringUtils.isBlank(qs) && StringUtils.isBlank(sj) && StringUtils.isBlank(ys)){
      	  obj.setSqnrqs("0");
      	  obj.setSqnrsj("0");
      	  obj.setSqnrys("0");
        }else{
	        if(qs!=null && qs.equals("清扫")){
	      	  obj.setSqnrqs("0");
	        }else{
	      	  obj.setSqnrqs("1");
	        }
	        if(sj!=null && sj.equals("收集")){
	      	  obj.setSqnrsj("0");
	        }else{
	      	  obj.setSqnrsj("1");
	        }
	        if(ys!=null && ys.equals("运输")){
	      	  obj.setSqnrys("0");
	        }else{
	      	  obj.setSqnrys("1");
	        }
        }
        //obj.setBgzs(bgzs);
        obj.setZzjgdm(orgCodeName);
        if(StringUtils.isNotBlank(agentMan)){
      	  	   agentMan=agentMan.replace("持法人代表委托书","");
      	  	   agentMan=agentMan.replace("(附件3)", "").trim();
      		   obj.setWtdlr(agentMan);
        }
        if(StringUtils.isBlank(agentMan)){
      	  agentMan= ExcelUtil.readData(sheet,1,8); 
      	  obj.setWtdlr(agentMan);
        }
        if(StringUtils.isNotBlank(regMoney)){
      	  regMoney=regMoney.replace("万元","");
      	  regMoney=regMoney.replace("(", "").replace(")","").replace("（", "").replace("）", "");
      	  
        }
        if(StringUtils.isNotBlank(remark)){
      	  remark=remark.replace("备注：","").trim();
        }
        obj.setYyzzzch(regCode);
        obj.setLxdh(telphone);
        //obj.setXmmc(projectName);
        obj.setCzdh(faxCode);
        obj.setFrdb(lawPerson);
        obj.setEmail(lawPersonEmail);
        obj.setYzbm(postCode);
        obj.setZczb(regMoney);
        obj.setWtdlr(agentMan);
        obj.setLxdh2(agentManTel);
        obj.setEmail2(agentManEmail);
        //张军 todo
        obj.setCzdh2(agentManFax);//set移动电话(agentManMobile);//此处不知是哪个字段,张军检查
        obj.setBz(remark);
        this.shljYsDao.save(obj); //请修改此处DAO调用  
        if(StringUtils.isNotBlank(obj.getQybh())){
  			YwComQyxxForm form=ywComQyxxService.get(Long.valueOf(obj.getQybh()));
  			form.setSqdw(obj.getSqdw());
  			form.setFddbr(obj.getFrdb());
  			form.setCz(faxCode);
  			form.setZczj(regMoney);
  			form.setEmail(obj.getEmail());
  			form.setPost(obj.getYzbm());
  			form.setQyphone(telphone);
  			form.setAddress(obj.getBgzs());
  			ywComQyxxService.updateYwComQyxx(form);
  		}
          
      
        }catch(Exception ee){
      	  ee.printStackTrace();
            rt="导入服务内容申请Excel文件时出错";
        }
        return rt;
    }
    
    /**
	 * 修改方法
	 * @param form
	 * @param tablename
	 */
	public void doUpdate(ShljYsForm form,String tablename) {
		StringBuffer sb=new StringBuffer(" update "+tablename+" set ");
		sb.append(" fzjg='"+form.getFzjg()+"',");
		sb.append(" projectname='"+form.getProjectName()+"',");
		sb.append(" xkcontent='"+form.getXkContent()+"',");
		sb.append(" zsbh='"+form.getZsbh()+"',");
//		sb.append(" startYear='"+form.getStartYear()+"',");
//		sb.append(" startMonth='"+form.getStartMonth()+"',");
//		sb.append(" startDay='"+form.getStartDay()+"',");
//		sb.append(" endYear='"+form.getEndYear()+"',");
//		sb.append(" endMonth='"+form.getEndMonth()+"',");
//		sb.append(" endDay='"+form.getEndDay()+"',");
//		sb.append(" fzYear='"+form.getFzYear()+"',");
//		sb.append(" fzMonth='"+form.getFzMonth()+"',");
//		sb.append(" fzDay='"+form.getFzDay()+"',");
		sb.append(" jdTel='"+form.getJdTel()+"',");
		//加了字段加入修改方法
//		if(StringUtils.isNotBlank(form.getStartYear()) &&
//			StringUtils.isNotBlank(form.getStartMonth()) && 
//			StringUtils.isNotBlank(form.getStartDay())){
//			  sb.append(" jysjsstr='"+form.getStartYear()+"年"+form.getStartMonth()+
//					"月"+form.getStartDay()+"日"+"',");//经营开始时间
//		}
//		if(StringUtils.isNotBlank(form.getEndYear()) &&
//				StringUtils.isNotBlank(form.getEndMonth()) && 
//				StringUtils.isNotBlank(form.getEndDay())){
//				  sb.append(" jysjestr='"+form.getEndYear()+"年"+form.getEndMonth()+
//						"月"+form.getEndDay()+"日"+"',");//经营结束时间
//	    }
//		if(StringUtils.isNotBlank(form.getFzYear()) &&
//				StringUtils.isNotBlank(form.getFzMonth()) && 
//				StringUtils.isNotBlank(form.getFzDay())){
//				  sb.append(" fzsjstr='"+form.getFzYear()+"年"+form.getFzMonth()+
//						"月"+form.getFzDay()+"日"+"',");//发证日期
//		}
		sb.append(" xmmc='"+form.getProjectName()+"' ");//项目名称
		
		sb.append(" where id="+form.getId());
		int i=this.shljYsDao.updateDateBase(sb.toString());
	}

	public void doUpdateFb(ShljYsForm form, String tablename) {
		StringBuffer sb=new StringBuffer(" update "+tablename+" set ");
		sb.append(" fzjg='"+form.getFzjg()+"',");
		sb.append(" projectname='"+form.getProjectName()+"',");
		sb.append(" xkcontent='"+form.getXkContent()+"',");
		sb.append(" fzh='"+form.getZsbh()+"',");//原来是zsbh证书编号现在修改为发证号
		sb.append(" startYear='"+form.getStartYear()+"',");
		sb.append(" startMonth='"+form.getStartMonth()+"',");
		sb.append(" startDay='"+form.getStartDay()+"',");
		sb.append(" endYear='"+form.getEndDay()+"',");
		sb.append(" endMonth='"+form.getEndMonth()+"',");
		sb.append(" endDay='"+form.getEndDay()+"',");
		sb.append(" fzYear='"+form.getFzYear()+"',");
		sb.append(" fzMonth='"+form.getFzMonth()+"',");
		sb.append(" fzDay='"+form.getFzDay()+"',");
		sb.append(" jdTel='"+form.getJdTel()+"',");
		//副本开始
		sb.append(" address='"+form.getAddress()+"',");
		sb.append(" fzrq='"+form.getFzrq()+"',");
		sb.append(" fzsjstr='"+form.getFzrq()+"',");
		sb.append(" qywszb='"+form.getQywszb()+"',");
		sb.append(" qygrzb='"+form.getQygrzb()+"',");
		sb.append(" qygyzb='"+form.getQygyzb()+"',");
		sb.append(" qyhzzb='"+form.getQyhzzb()+"',");
		sb.append(" qylx='"+form.getQylx()+"',");
		sb.append(" qyManagerZc='"+form.getQyManagerZc()+"',");
		sb.append(" qyMaangerZw='"+form.getQyMaangerZw()+"',");
		sb.append(" qyManager='"+form.getQyManager()+"',");
		sb.append(" fddbrZw='"+form.getFddbrZw()+"',");
		sb.append(" frdb='"+form.getFddbr()+"',");
		sb.append(" fddbrZc='"+form.getFddbrZc()+"',");
		sb.append(" zczb='"+form.getZczb()+"',");
		if(StringUtils.isNotBlank(form.getXmnr1())){
			sb.append(" xmnr1='"+form.getXmnr1()+"', ");//项目名称
			sb.append(" startday1='"+form.getStartday1()+"', ");//项目记录
			sb.append(" endday1='"+form.getEndday1()+"', ");//项目记录
			sb.append(" gzsj1='"+form.getGzsj1()+"', ");//项目记录
		}
		if(StringUtils.isNotBlank(form.getXmnr2())){
			sb.append(" xmnr2='"+form.getXmnr2()+"', ");//项目名称
			sb.append(" startday2='"+form.getStartday2()+"', ");//项目记录
			sb.append(" endday2='"+form.getEndday2()+"', ");//项目记录
			sb.append(" gzsj2='"+form.getGzsj2()+"', ");//项目记录
		}
		if(StringUtils.isNotBlank(form.getXmnr3())){
			sb.append(" xmnr3='"+form.getXmnr3()+"', ");//项目名称
			sb.append(" startday3='"+form.getStartday3()+"', ");//项目记录
			sb.append(" endday3='"+form.getEndday3()+"',");//项目记录
			sb.append(" gzsj3='"+form.getGzsj3()+"', ");//项目记录
		}
		if(StringUtils.isNotBlank(form.getXmnr4())){
			sb.append(" xmnr4='"+form.getXmnr4()+"', ");//项目名称
			sb.append(" startday4='"+form.getStartday4()+"', ");//项目记录
			sb.append(" endday4='"+form.getEndday4()+"',");//项目记录
			sb.append(" gzsj4='"+form.getGzsj4()+"', ");//项目记录
		}
		if(StringUtils.isNotBlank(form.getXmnr5())){
			sb.append(" xmnr5='"+form.getXmnr5()+"', ");//项目名称
			sb.append(" startday5='"+form.getStartday5()+"', ");//项目记录
			sb.append(" endday5='"+form.getEndday5()+"',");//项目记录
			sb.append(" gzsj5='"+form.getGzsj5()+"', ");//项目记录
		}
		if(StringUtils.isNotBlank(form.getXmnr6())){
			sb.append(" xmnr6='"+form.getXmnr6()+"', ");//项目名称
			sb.append(" startday6='"+form.getStartday6()+"', ");//项目记录
			sb.append(" endday6='"+form.getEndday6()+"',");//项目记录
			sb.append(" gzsj6='"+form.getGzsj6()+"', ");//项目记录
		}
		
		if(StringUtils.isNotBlank(form.getXmnr7())){
			sb.append(" xmnr7='"+form.getXmnr7()+"', ");//项目名称
			sb.append(" startday7='"+form.getStartday7()+"', ");//项目记录
			sb.append(" endday7='"+form.getEndday7()+"',");//项目记录
			sb.append(" gzsj7='"+form.getGzsj7()+"', ");//项目记录
		}
		if(StringUtils.isNotBlank(form.getXmnr8())){
			sb.append(" xmnr8='"+form.getXmnr8()+"', ");//项目名称
			sb.append(" startday8='"+form.getStartday8()+"', ");//项目记录
			sb.append(" endday8='"+form.getEndday8()+"',");//项目记录
			sb.append(" gzsj8='"+form.getGzsj8()+"', ");//项目记录
		}
		if(StringUtils.isNotBlank(form.getXmnr9())){
			sb.append(" xmnr9='"+form.getXmnr9()+"', ");//项目名称
			sb.append(" startday9='"+form.getStartday9()+"', ");//项目记录
			sb.append(" endday9='"+form.getEndday9()+"',");//项目记录
			sb.append(" gzsj9='"+form.getGzsj9()+"', ");//项目记录
		}
		
		//加了字段加入修改方法
		if(StringUtils.isNotBlank(form.getStartYear()) &&
			StringUtils.isNotBlank(form.getStartMonth()) && 
			StringUtils.isNotBlank(form.getStartDay())){
			  sb.append(" jysjsstr='"+form.getStartYear()+"年"+form.getStartMonth()+
//					"月"+form.getStartDay()+"日"+"',");//经营开始时间
			  		"月"+form.getStartDay()+"',");//经营开始时间
		}
		if(StringUtils.isNotBlank(form.getEndYear()) &&
				StringUtils.isNotBlank(form.getEndMonth()) && 
				StringUtils.isNotBlank(form.getEndDay())){
				  sb.append(" jysjestr='"+form.getEndYear()+"年"+form.getEndMonth()+
//						"月"+form.getEndDay()+"日"+"',");//经营结束时间
				  		"月"+form.getEndDay()+"',");//经营结束时间
	    }
//		if(StringUtils.isNotBlank(form.getFzYear()) &&
//				StringUtils.isNotBlank(form.getFzMonth()) && 
//				StringUtils.isNotBlank(form.getFzDay())){
//				  sb.append(" fzsjstr='"+form.getFzYear()+"年"+form.getFzMonth()+
//						"月"+form.getFzDay()+"日"+"',");//发证日期
//		}
		sb.append(" xmmc='"+form.getProjectName()+"' ");//项目名称		
		sb.append(" where id="+form.getId());
		System.out.println(" ============= "+sb.toString());
		shljYsDao.updateDateBase(sb.toString());
		
	}
	
	/*
	 * 主要用于证书打印(non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.ICsshljCzService#getForm(java.lang.String, java.lang.String)
	 */
	public ShljYsForm getForm(Long id, String tablename) {
		String sql=" select *　from "+tablename+" where id="+id;
		List list=shljYsDao.queryForList(sql);
		ShljYsForm form=new ShljYsForm();
		  if(list!=null && list.size()>0){
   		   Map<String,String> map=(Map<String,String>) list.get(0);
             form.setId(Long.valueOf((String)map.get("id")));
             form.setAcceptNo(map.get("accept_no"));
             form.setEmail(map.get("email"));
             form.setZczb(map.get("zczb"));
             form.setWtdlr(map.get("wtdlr"));
     
             form.setYzbm(map.get("yzbm"));
             form.setEmail2(map.get("email2"));
             form.setLxdh(map.get("lxdh"));
             form.setLxdh2(map.get("lxdh2"));
             form.setSqdw(map.get("qymc"));
             form.setSqnrqs(map.get("sqnrqs"));
             form.setSqnrsj(map.get("sqnrsj"));
             form.setSqnrys(map.get("sqnrys"));
             form.setZsbh(map.get("fzh"));
             form.setYyzch(map.get("yyzzzch"));//营业注册号码form里面多余了一个
             form.setYyzzzch(map.get("yyzzzch"));//营业注册号码
             form.setFddbr(map.get("frdb"));
             form.setProjectName(map.get("xmmc"));//项目名称
             form.setXkContent((map.get("xkcontent")));
             String startDate=map.get("jysjsstr");//经营开始时间
             String endDate=map.get("jysjestr");//经营结束时间
             String fzsj=map.get("fzsjstr");//发证时间
             if(StringUtils.isBlank(fzsj)){
            	 fzsj=map.get("fzrq");
              }
             if(StringUtils.isBlank(map.get("xkcontent"))){
            	 form.setXkContent("（水域）城市生活垃圾经营性清扫（捞）、收集服务许可");
             }
             if(StringUtils.isNotBlank(startDate) && startDate.length()>=10){
     			form.setStartYear(startDate.substring(0,4));
     			form.setStartMonth(startDate.substring(5,7));
     			form.setStartDay(startDate.substring(8,startDate.length()));
     		 }else{
     			form.setStartYear(DateUtilsEX.getCurYear());
     			form.setStartMonth(DateUtilsEX.getCurMonth());
     			form.setStartDay(DateUtilsEX.getCurDay());
     		 }
             if(StringUtils.isNotBlank(endDate) && endDate.length()>=10){
            	   form.setEndYear(endDate.substring(0,4));
        			form.setEndMonth(endDate.substring(5,7));
        			form.setEndDay(endDate.substring(8,endDate.length()));
      		}else{
      			form.setEndYear(DateUtilsEX.getCurYear());
      			form.setEndMonth(DateUtilsEX.getCurMonth());
      			form.setEndDay(DateUtilsEX.getCurDay());
      		}
            if(StringUtils.isNotBlank(fzsj) && fzsj.length()>=10){
       			form.setFzYear(fzsj.substring(0,4));
       			form.setFzMonth(fzsj.substring(5,7));
       			form.setFzDay(fzsj.substring(8,10));
       		}else{
    			form.setFzYear(DateUtilsEX.getCurYear());
      			form.setFzMonth(DateUtilsEX.getCurMonth());
      			form.setFzDay(DateUtilsEX.getCurDay());
       		}
//             form.setProjectName(map.get("projectname"));
//             form.setXkContent((map.get("xkcontent")));
             //form.setStartYear(map.get("startyear"));
             //form.setStartMonth(map.get("startmonth"));
             //form.setStartDay(map.get("startday"));
             //form.setEndDay(map.get("endday"));
             //form.setEndMonth(map.get("endmonth"));
             //form.setEndYear(map.get("endyear"));
             //form.setFzDay(map.get("fzday"));
             //form.setFzYear(map.get("fzyear"));
             form.setJdTel(map.get("jdtel"));
             form.setCzdh(map.get("czdh"));
             form.setCzdh2(map.get("czdh"));
             //form.setFzMonth(map.get("fzmonth"));
             form.setAddress(map.get("address"));
             form.setQyMaangerZw(map.get("qymaangerzw"));
             form.setQyManager(map.get("qymanager"));
             form.setQyManagerZc(map.get("qymanagerzc"));
             form.setQylx(map.get("qylx"));
             form.setQygyzb(map.get("qygyzb"));
             form.setQygrzb(map.get("qygrzb"));
             form.setQyhzzb(map.get("qyhzzb"));
             form.setQywszb(map.get("qywszb"));
             form.setFddbrZc(map.get("fddbrzc"));
             form.setFddbrZw(map.get("fddbrzw"));
             form.setAddress(map.get("address"));
             form.setZczb(map.get("zczb"));
             form.setZzjgdm(map.get("zzjgdm"));
             form.setFzjg(map.get("fzjg")==null?"广州市城市管理委员会":map.get("fzjg"));
             
             form.setCbyw(map.get("cbyw"));
             form.setSjr(map.get("sjr"));
             form.setFzrq(fzsj);
             
 			//设置收件通知书书签
             form.setCbyw2(form.getCbyw());
             form.setCbyw3(form.getCbyw());
             form.setCbyw4(form.getCbyw());
             form.setFrdb2(form.getFddbr());
             form.setFrdb(form.getFddbr());
             form.setAcceptNo2(map.get("accept_no"));
//             String tbrqStr = simpleDateFormat.format(form.getTbrq());
             form.setTbrq(map.get("slrq"));
             form.setTbrq2(map.get("slrq"));
             form.setCurDate2(map.get("slrq"));
             form.setCurDate3(map.get("slrq"));
             form.setSjr2(form.getSjr());
             form.setSajg(form.getFzjg());
             form.setSajg2(form.getFzjg());
             form.setQymc2(form.getSqdw());
             form.setQymc(form.getSqdw());
             form.setXmnr1(map.get("xmnr1"));//副证的项目登记
             form.setXmnr2(map.get("xmnr2")==null?"":map.get("xmnr2"));//副证的项目登记
             form.setXmnr3(map.get("xmnr3"));//副证的项目登记
             form.setStartday1(map.get("startday1"));
             form.setStartday2(map.get("startday2"));
             form.setStartday3(map.get("startday3"));
             form.setEndday1(map.get("endday1"));
             form.setEndday2(map.get("endday2"));
             form.setEndday3(map.get("endday3"));
             form.setGzsj1(map.get("gzsj1"));
             form.setGzsj2(map.get("gzsj2"));
             form.setGzsj3(map.get("gzsj3"));
             form.setXmnr4(map.get("xmnr4"));//副证的项目登记
             form.setXmnr5(map.get("xmnr5"));//副证的项目登记
             form.setXmnr6(map.get("xmnr6"));//副证的项目登记
             form.setStartday4(map.get("startday4"));
             form.setStartday5(map.get("startday5"));
             form.setStartday6(map.get("startday6"));
             form.setEndday4(map.get("endday4"));
             form.setEndday5(map.get("endday5"));
             form.setEndday6(map.get("endday6"));
             form.setGzsj4(map.get("gzsj4"));
             form.setGzsj5(map.get("gzsj5"));
             form.setGzsj6(map.get("gzsj6"));
             form.setXmnr7(map.get("xmnr7"));//副证的项目登记
             form.setXmnr8(map.get("xmnr8"));//副证的项目登记
             form.setXmnr9(map.get("xmnr9"));//副证的项目登记
             form.setStartday7(map.get("startday7"));
             form.setStartday8(map.get("startday8"));
             form.setStartday9(map.get("startday9"));
             form.setEndday7(map.get("endday7"));
             form.setEndday8(map.get("endday8"));
             form.setEndday9(map.get("endday9"));
             form.setGzsj7(map.get("gzsj7"));
             form.setGzsj8(map.get("gzsj8"));
             form.setGzsj9(map.get("gzsj9"));
             
             
             
             List list2 =new ArrayList();
             list2 = ywHwXminfoService.getFbXminfo(id, "shljys");
             if(list2!=null && list2.size()>0){
                 Map<String, String> map2 =(Map<String, String>) list2.get(0);
                 form.setXmid1(Long.parseLong(map2.get("xmid")));
                 form.setXmnr1(map2.get("xmnr"));
                 form.setStartday1(map2.get("startday"));
                 form.setEndday1(map2.get("endday"));
                 form.setGzsj1(map2.get("gzsj"));
                 if(list2.size()>1){
                	 map2 =(Map<String, String>) list2.get(1);
                     form.setXmid2(Long.parseLong(map2.get("xmid")));
                     form.setXmnr2(map2.get("xmnr"));
                     form.setStartday2(map2.get("startday"));
                     form.setEndday2(map2.get("endday"));
                     form.setGzsj2(map2.get("gzsj"));
                 }
                 if(list2.size()>2){
                	 map2 =(Map<String, String>) list2.get(2);
                     form.setXmid3(Long.parseLong(map2.get("xmid")));
                     form.setXmnr3(map2.get("xmnr"));
                     form.setStartday3(map2.get("startday"));
                     form.setEndday3(map2.get("endday"));
                     form.setGzsj3(map2.get("gzsj"));
                 }
                 if(list2.size()>3){
                	 map2 =(Map<String, String>) list2.get(3);
                     form.setXmid4(Long.parseLong(map2.get("xmid")));
                     form.setXmnr4(map2.get("xmnr"));
                     form.setStartday4(map2.get("startday"));
                     form.setEndday4(map2.get("endday"));
                     form.setGzsj4(map2.get("gzsj"));
                 }
                 if(list2.size()>4){
                	 map2 =(Map<String, String>) list2.get(4);
                     form.setXmid5(Long.parseLong(map2.get("xmid")));
                     form.setXmnr5(map2.get("xmnr"));
                     form.setStartday5(map2.get("startday"));
                     form.setEndday5(map2.get("endday"));
                     form.setGzsj5(map2.get("gzsj"));
                 }
                 if(list2.size()>5){
                	 map2 =(Map<String, String>) list2.get(5);
                     form.setXmid6(Long.parseLong(map2.get("xmid")));
                     form.setXmnr6(map2.get("xmnr"));
                     form.setStartday6(map2.get("startday"));
                     form.setEndday6(map2.get("endday"));
                     form.setGzsj6(map2.get("gzsj"));
                 }
             }
          }
		return form;
	}

    /**
     * 返回模版id
     * @param templateCode 模版材料名称
     * @return
     */
    private Long getTempId(String templateCode,String filename){
        //查询材料模版
        List<YwComSjcllist> sjcllists = ywComSjcllistService.getYwComSjcls(templateCode);
        Long tempId=0l;
        for(YwComSjcllist entity:sjcllists){
            String title=entity.getTitle();
            if(title.indexOf(filename)>=0){
                tempId=entity.getId();
                break;
            }
        }
        return tempId;
    }
    

    /**
     * 获取所有的收件材料
     * @param qyid
     * @param ajid
     * @param templateCode
     * @return
     */
    private List<YwComSjclForm> getFiles(Long qyid,Long ajid,String templateCode){
        YwComSjclForm fkForm = new YwComSjclForm();
        fkForm.setFkid(qyid);
        fkForm.setAjid(ajid);
        fkForm.setTplcode(templateCode);
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        return sjclTpl;
    }
    

    /**
     * 是否是正确的文件格式
     * @param wb
     * @return
     */
    private boolean isCorrectFile(Workbook wb,int sheetsize){
        Sheet[] sheets=wb.getSheets();
        if(sheets.length>=sheetsize)
            return true;
        else
            return false;
    }
    
    /**
     * 获取申请表文件
     * @param sjcls
     * @return
     */
    public String getSqbFilePath(Map map){
    	 String templateCode=(String) map.get("templateCode");
    	 String idStr = (String) map.get("id");
         Long ajid= Long.parseLong(idStr) ;
         String contextpath=(String) map.get("contextPath");
         String fileName=(String) map.get("filename");
         Long tempId=getTempId(templateCode,fileName);
        List<YwComSjclForm> sjcls = getFiles(null, Long.valueOf(ajid), templateCode);
        String filepath="";
        Workbook wb;
        try {
            for(YwComSjclForm entity:sjcls){
                if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                    filepath=entity.getFilepath();
                    if(StringUtils.isNotBlank(filepath)){
                        String[] paths=filepath.split(",");
                    for(String path:paths){
                            path=path.replace("/", File.separator);
                            File file=new File(contextpath+path);
                            String name=entity.getFilename();
                            if(StringUtils.isNotBlank(name)){
                                for(String filename:name.split(",")){
                                filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                                if(!"xls".equals(filename)){
                                    continue;
                                }
                                if(!file.exists()){
                                    continue;
                                }else{
                                    wb = Workbook.getWorkbook(file);
                                    if(isCorrectFile(wb,1)){
                                        return path;
                                    }
                               }
                              }
                            }
                       
                        }
                    }
                }
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        return filepath;
    }

	public List getListById2(Long id,List list){
	    String sql="from ShljYs ps where id="+id;
        return shljYsDao.find(sql,list);
    }
	
	

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.smartformCsdlwj.service.ISmartFormShljqsService#writeFbData(com.augurit.gzcgwsp.smartformCsdlwj.web.form.ShljqsForm, java.lang.String, java.lang.String)
	 */
	public String writeFbData(ShljYsForm form, String context, String inpath) {
		inpath=inpath.replace("/", File.separator);
        String tempOutPath="Templates/csshlj/outfile/xkzfb/outfile";
        String fileName = GetFileName.getFileName("xls");
        tempOutPath=this.buildPath(tempOutPath, fileName);
        String outfilepath=context+"/"+tempOutPath;
        outfilepath=outfilepath.replace("/", File.separator);
        String outdir=outfilepath.substring(0,outfilepath.lastIndexOf(File.separator));
        File file=new File(outdir);
        if(!file.exists()){
            file.mkdirs();
        }
        writerFbDataExcel(inpath,outfilepath,form);
        
		return tempOutPath;
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.smartformCsdlwj.service.ISmartFormShljqsService#writeXkzData(com.augurit.gzcgwsp.smartformCsdlwj.web.form.ShljqsForm, java.lang.String, java.lang.String)
	 */
	public String writeXkzData(ShljYsForm form, String context, String inpath) {
		inpath=inpath.replace("/", File.separator);
        String tempOutPath="Templates/csshlj/outfile";
        String fileName = GetFileName.getFileName( "xls");
        tempOutPath=this.buildPath(tempOutPath, fileName);
        String outfilepath=context+"/"+tempOutPath;
        outfilepath=outfilepath.replace("/", File.separator);
        String outdir=outfilepath.substring(0,outfilepath.lastIndexOf(File.separator));
        File file=new File(outdir);
        if(!file.exists()){
            file.mkdirs();
        }
        writerDataExcel(inpath,outfilepath,form);
        try {
			FileUtil.delDir(outdir, fileName);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tempOutPath;
	}

    /**
     * 复制模版写入数据
     * @param inpath
     * @param outfilepath
     * @param form
     */
    private void writerDataExcel(String inpath, String outfilepath,
    		ShljYsForm form) {
        WritableWorkbook wwb;
        try {
            wwb = ExcelUtil.createWorkbookByOld(inpath, outfilepath);
            WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
            copyZsData(wsOne,form);
            wwb.write();
            wwb.close();
        } catch (Exception e) {
            log.error("写文件错误"+e);
        } 
    }
    /**
     * 复制副本写入数据
     * @param inpath
     * @param outfilepath
     * @param form
     */
    private void writerFbDataExcel(String inpath, String outfilepath,
    		ShljYsForm form) {
        WritableWorkbook wwb;
        try {
            wwb = ExcelUtil.createWorkbookByOld(inpath, outfilepath);
            WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
            copyFbData(wsOne,form);
            WritableSheet wsTwo = ExcelUtil.getWritableSheetByIndex(wwb, 1);
            copyFbData2(wsTwo,form);
            wwb.write();
            wwb.close();
        } catch (Exception e) {
            log.error("写文件错误"+e);
        } 
    }

    /**
     * 写数据
     * @param wsOne
     * @param form
     */
    private void copyFbData(WritableSheet wsOne, ShljYsForm form) {
    	 try {
    		 WritableCellFormat wcf=ExcelUtil.createCellFormat("宋体", 14, Alignment.LEFT, VerticalAlignment.CENTRE,false);
			 WritableCellFormat wcf1=ExcelUtil.createCellFormat("宋体", 14, Alignment.LEFT, VerticalAlignment.CENTRE,true);
			 WritableCellFormat wcf2=ExcelUtil.createCellFormat("宋体", 12, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
			 WritableCellFormat wcf3=ExcelUtil.createCellFormat("宋体", 14, Alignment.LEFT, VerticalAlignment.TOP,false);
			 WritableCellFormat wcfdate=ExcelUtil.createCellFormat("宋体", 8, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
			 WritableCellFormat wcfxmnr=ExcelUtil.createCellFormat("宋体", 10, Alignment.LEFT, VerticalAlignment.TOP,true);
			 WritableCellFormat wcfxmrq=ExcelUtil.createCellFormat("宋体", 10, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
			 wcf.setLocked(false);
			 wcf1.setLocked(false);
			 wcf2.setLocked(false);
			 wcf3.setLocked(false);
			 wcfdate.setLocked(false);
			 ExcelUtil.modiStrCell2(wsOne, 1, 1, form.getSqdw()==null?"":form.getSqdw(), wcf);//申请单位
			 ExcelUtil.modiStrCell2(wsOne,1, 2, form.getAddress()==null?"":form.getAddress(), wcf1);//地址
			 ExcelUtil.modiStrCell2(wsOne,1, 3, form.getYyzch()==null?"":form.getYyzch(), wcf);//营业执照注册号
			 ExcelUtil.modiStrCell2(wsOne,1, 4, form.getZczb()==null?"":form.getZczb(), wcf);//注册资本
			 ExcelUtil.modiStrCell2(wsOne,1, 6, form.getFddbr()==null?"":form.getFddbr(), wcf2);//法定代表人
			 ExcelUtil.modiStrCell2(wsOne,8, 6, form.getFddbrZw()==null?"":form.getFddbrZw(), wcf2);//职务
			 ExcelUtil.modiStrCell2(wsOne,17, 6, form.getFddbrZc()==null?"":form.getFddbrZc(), wcf2);//职称
			 ExcelUtil.modiStrCell2(wsOne,1, 8, form.getQyManager()==null?"":form.getQyManager(), wcf2);//企业负责人
			 ExcelUtil.modiStrCell2(wsOne,8, 8,form.getQyMaangerZw()==null?"":form.getQyMaangerZw(), wcf2);//职务
			 ExcelUtil.modiStrCell2(wsOne,17, 8,form.getQyManagerZc()==null?"":form.getQyManagerZc(), wcf2);//职称
			 if("1".equals(form.getQylx())){//内资企业
				  ExcelUtil.modiStrCell2(wsOne,1, 10,"√", wcf2);//企业类型内资
			 }else if ("2".equals(form.getQylx())){
				  ExcelUtil.modiStrCell2(wsOne,9, 10,"√", wcf2);//企业类型 港澳台
			 } else if ("2".equals(form.getQylx())){
			  ExcelUtil.modiStrCell2(wsOne,1, 11,"√", wcf2);//企业类型 外商
			}else if ("2".equals(form.getQylx())){
			  ExcelUtil.modiStrCell2(wsOne,9, 11,"√", wcf2);//企业类型 台资
			}
      
			 ExcelUtil.modiStrCell2(wsOne,2, 14, form.getQygyzb()==null?"":form.getQygyzb(), wcf2);//企业资本构成比例 国有
			 ExcelUtil.modiStrCell2(wsOne,11, 14, form.getQygrzb()==null?"":form.getQygrzb(), wcf2);//企业资本构成比例 个人
			 ExcelUtil.modiStrCell2(wsOne,2, 15, form.getQywszb()==null?"":form.getQywszb(), wcf2);//企业资本构成比例 外商
			 ExcelUtil.modiStrCell2(wsOne,13, 15, form.getQyhzzb()==null?"":form.getQyhzzb(), wcf2);//企业资本构成比例 港澳
			 
			 ExcelUtil.modiStrCell2(wsOne,1, 18, form.getXkContent()==null?"":form.getXkContent(), wcf2);//许可正内容
			 
			 ExcelUtil.modiStrCell2(wsOne,1, 20, form.getFzjg()==null?"":form.getFzjg(), wcf2);//许可发证机关
			 
			 ExcelUtil.modiStrCell2(wsOne,15, 21, form.getJdTel()==null?"":form.getJdTel(), wcf2);//监督电话
			 
			 ExcelUtil.modiStrCell2(wsOne,1, 23, form.getStartYear()==null?"":form.getStartYear(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,4, 23, form.getStartMonth()==null?"":form.getStartMonth(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,7, 23, form.getStartDay()==null?"":form.getStartDay(), wcf2);//许可正发证日期
      
			 ExcelUtil.modiStrCell2(wsOne,10, 23, form.getEndYear()==null?"":form.getEndYear(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,13, 23, form.getEndMonth()==null?"":form.getEndMonth(), wcf2);//许可正发证日期

			 ExcelUtil.modiStrCell2(wsOne,16, 23, form.getEndDay()==null?"":form.getEndDay(), wcf2);//许可正发证日期
      
			 ExcelUtil.modiStrCell2(wsOne,1, 25, form.getFzrq()==null?"":form.getFzrq(), wcf2);//许可正发证日期
			 ExcelUtil.modiStrCell2(wsOne,18, 25, form.getZsbh()==null?"":form.getZsbh(), wcf2);//许可正发证日期

             List list2 =new ArrayList();
             list2 = ywHwXminfoService.getFbXminfo(form.getId(), "shljys");
             if(list2!=null && list2.size()>0){
                 Map<String, String> map2 =(Map<String, String>) list2.get(0);
                 String xmnr =map2.get("xmnr");
                 xmnr = xmnr.replaceAll("<br>", "\r\n");
                 xmnr = xmnr.replaceAll("&nbsp;", " ");
                 xmnr +="\r\n  有效期限：";
                 String startday = map2.get("startday");
                 String endday = map2.get("endday");
                 if(startday!=null && startday.length()>0 && endday!=null && endday.length()>0){
                	 xmnr +="从"+startday+"到"+endday;
                 }
    			 ExcelUtil.modiStrCell2(wsOne,22, 2, xmnr==null?"":xmnr, wcfxmnr);//项目内容
                 String fzsj = map2.get("gzsj");
                 String year="";
                 String month="";
                 String day="";
                 if(fzsj!=null && fzsj.length()>0){
                	 year = fzsj.substring(0, 4);
                	 month = fzsj.substring(5, 7);
                	 day = fzsj.substring(8, 10);
        			 ExcelUtil.modiStrCell2(wsOne,27, 5, year==null?"":year, wcfxmrq);//变更盖章年
        			 ExcelUtil.modiStrCell2(wsOne,29, 5, month==null?"":month, wcfxmrq);//变更盖章月
        			 ExcelUtil.modiStrCell2(wsOne,31, 5, day==null?"":day, wcfxmrq);//变更盖章日
                 }
                 if(list2.size()>1){
                	 map2 =(Map<String, String>) list2.get(1);
                	 xmnr =map2.get("xmnr");
                     xmnr = xmnr.replaceAll("<br>", "\r\n");
                     xmnr = xmnr.replaceAll("&nbsp;", " ");
                     xmnr +="\r\n  有效期限：";
                     startday = map2.get("startday");
                     endday = map2.get("endday");
                     if(startday!=null && startday.length()>0 && endday!=null && endday.length()>0){
                    	 xmnr +="从"+startday+"到"+endday;
                     }
        			 ExcelUtil.modiStrCell2(wsOne,22, 8, xmnr==null?"":xmnr, wcfxmnr);//项目内容
                     fzsj = map2.get("gzsj");
                     year="";
                     month="";
                     day="";
                     if(fzsj!=null && fzsj.length()>0){
                    	 year = fzsj.substring(0, 4);
                    	 month = fzsj.substring(5, 7);
                    	 day = fzsj.substring(8, 10);
            			 ExcelUtil.modiStrCell2(wsOne,27, 16, year==null?"":year, wcfxmrq);//变更盖章年
            			 ExcelUtil.modiStrCell2(wsOne,29, 16, month==null?"":month, wcfxmrq);//变更盖章月
            			 ExcelUtil.modiStrCell2(wsOne,31, 16, day==null?"":day, wcfxmrq);//变更盖章日
                     }
                 }
                 if(list2.size()>2){
                	 map2 =(Map<String, String>) list2.get(1);
                	 xmnr =map2.get("xmnr");
                     xmnr = xmnr.replaceAll("<br>", "\r\n");
                     xmnr = xmnr.replaceAll("&nbsp;", " ");
                     xmnr +="\r\n  有效期限：";
                     startday = map2.get("startday");
                     endday = map2.get("endday");
                     if(startday!=null && startday.length()>0 && endday!=null && endday.length()>0){
                    	 xmnr +="从"+startday+"到"+endday;
                     }
        			 ExcelUtil.modiStrCell2(wsOne,22, 20, xmnr==null?"":xmnr, wcfxmnr);//项目内容
                     fzsj = map2.get("gzsj");
                     year="";
                     month="";
                     day="";
                     if(fzsj!=null && fzsj.length()>0){
                    	 year = fzsj.substring(0, 4);
                    	 month = fzsj.substring(5, 7);
                    	 day = fzsj.substring(8, 10);
            			 ExcelUtil.modiStrCell2(wsOne,27, 26, year==null?"":year, wcfxmrq);//变更盖章年
            			 ExcelUtil.modiStrCell2(wsOne,29, 26, month==null?"":month, wcfxmrq);//变更盖章月
            			 ExcelUtil.modiStrCell2(wsOne,31, 26, day==null?"":day, wcfxmrq);//变更盖章日
                     }
                 }
             }
		} catch (Exception e) {
			e.printStackTrace();
		} 
         
        
    }
    
    /**
     * 写副本第二页数据
     * @param wsOne
     * @param form
     */
    private void copyFbData2(WritableSheet wsOne, ShljYsForm form) {
    	 try {
			 WritableCellFormat wcf2=ExcelUtil.createCellFormat("宋体", 10, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
			 WritableCellFormat wcfxmnr=ExcelUtil.createCellFormat("宋体", 10, Alignment.LEFT, VerticalAlignment.TOP,true);
			 wcf2.setLocked(false);
             List list2 =new ArrayList();
             list2 = ywHwXminfoService.getFbXminfo(form.getId(), "shljys");
             if(list2!=null && list2.size()>3){
                 Map<String, String> map2 =(Map<String, String>) list2.get(3);
                 String xmnr =map2.get("xmnr");
                 xmnr = xmnr.replaceAll("<br>", "\r\n");
                 xmnr = xmnr.replaceAll("&nbsp;", " ");
                 xmnr +="\r\n  有效期限：";
                 String startday = map2.get("startday");
                 String endday = map2.get("endday");
                 if(startday!=null && startday.length()>0 && endday!=null && endday.length()>0){
                	 xmnr +="从"+startday+"到"+endday;
                 }
    			 ExcelUtil.modiStrCell2(wsOne,0, 3, xmnr==null?"":xmnr, wcfxmnr);//项目内容
                 String fzsj = map2.get("gzsj");
                 String year="";
                 String month="";
                 String day="";
                 if(fzsj!=null && fzsj.length()>4){
                	 year = fzsj.substring(0, 4);
                	 month = fzsj.substring(5, 7);
                	 day = fzsj.substring(8, 10);
        			 ExcelUtil.modiStrCell2(wsOne,10, 7, year==null?"":year, wcf2);//变更盖章年
        			 ExcelUtil.modiStrCell2(wsOne,13, 7, month==null?"":month, wcf2);//变更盖章月
        			 ExcelUtil.modiStrCell2(wsOne,15, 7, day==null?"":day, wcf2);//变更盖章日
                 }
                 if(list2.size()>4){
                	 map2 =(Map<String, String>) list2.get(4);
                	 xmnr =map2.get("xmnr");
                     xmnr = xmnr.replaceAll("<br>", "\r\n");
                     xmnr = xmnr.replaceAll("&nbsp;", " ");
                     xmnr +="\r\n  有效期限：";
                     startday = map2.get("startday");
                     endday = map2.get("endday");
                     if(startday!=null && startday.length()>0 && endday!=null && endday.length()>0){
                    	 xmnr +="从"+startday+"到"+endday;
                     }
        			 ExcelUtil.modiStrCell2(wsOne,0, 9, xmnr==null?"":xmnr, wcfxmnr);//项目内容
                     fzsj = map2.get("gzsj");
                     year="";
                     month="";
                     day="";
                     if(fzsj!=null && fzsj.length()>0){
                    	 year = fzsj.substring(0, 4);
                    	 month = fzsj.substring(5, 7);
                    	 day = fzsj.substring(8, 10);
            			 ExcelUtil.modiStrCell2(wsOne,10, 16, year==null?"":year, wcf2);//变更盖章年
            			 ExcelUtil.modiStrCell2(wsOne,13, 16, month==null?"":month, wcf2);//变更盖章月
            			 ExcelUtil.modiStrCell2(wsOne,15, 16, day==null?"":day, wcf2);//变更盖章日
                     }
                 }
                 if(list2.size()>5){
                	 map2 =(Map<String, String>) list2.get(5);
                	 xmnr =map2.get("xmnr");
                     xmnr = xmnr.replaceAll("<br>", "\r\n");
                     xmnr = xmnr.replaceAll("&nbsp;", " ");
                     xmnr +="\r\n  有效期限：";
                     startday = map2.get("startday");
                     endday = map2.get("endday");
                     if(startday!=null && startday.length()>0 && endday!=null && endday.length()>0){
                    	 xmnr +="从"+startday+"到"+endday;
                     }
        			 ExcelUtil.modiStrCell2(wsOne,0, 19, xmnr==null?"":xmnr, wcfxmnr);//项目内容
                     fzsj = map2.get("gzsj");
                     year="";
                     month="";
                     day="";
                     if(fzsj!=null && fzsj.length()>0){
                    	 year = fzsj.substring(0, 4);
                    	 month = fzsj.substring(5, 7);
                    	 day = fzsj.substring(8, 10);
            			 ExcelUtil.modiStrCell2(wsOne,10, 26, year==null?"":year, wcf2);//变更盖章年
            			 ExcelUtil.modiStrCell2(wsOne,13, 26, month==null?"":month, wcf2);//变更盖章月
            			 ExcelUtil.modiStrCell2(wsOne,15, 26, day==null?"":day, wcf2);//变更盖章日
                     }
                 }
             }
             
		} catch (Exception e) {
			e.printStackTrace();
		} 
         
        
    }
    
    /**
     * 写证书数据
     * @param wsOne
     * @param form
     */
    private void copyZsData(WritableSheet wsOne, ShljYsForm form) {
       
        try {
            WritableCellFormat wcf=ExcelUtil.createCellFormat("宋体", 20, Alignment.LEFT, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf5=ExcelUtil.createCellFormat("宋体", 20, Alignment.RIGHT, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf1=ExcelUtil.createCellFormat("宋体", 20, Alignment.LEFT, VerticalAlignment.CENTRE,true);
            WritableCellFormat wcf2=ExcelUtil.createCellFormat("宋体", 20, Alignment.LEFT, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf3=ExcelUtil.createCellFormat("宋体", 20, Alignment.CENTRE, VerticalAlignment.CENTRE,false);
            WritableCellFormat wcf4=ExcelUtil.createCellFormat("宋体", 20, Alignment.RIGHT, VerticalAlignment.CENTRE,false);
            ExcelUtil.modiStrCell2(wsOne, 8,22, form.getZsbh()==null?"":form.getZsbh(), wcf);//编号
            ExcelUtil.modiStrCell2(wsOne, 0, 8, form.getSqdw()==null?"":form.getSqdw(), wcf5);//申请单位
            //ExcelUtil.modiStrCell2(wsOne, 10, 16, "广州市城市委员会", wcf);//发证机关
            ExcelUtil.modiStrCell2(wsOne, 3, 20, form.getXkContent()==null?"":form.getXkContent(), wcf);//许可内容
            //ExcelUtil.modiStrCell2(wsOne, 3, 21, form.getProjectName()==null?"":form.getProjectName(), wcf);//项目名称
           // ExcelUtil.modiStrCell2(wsOne, 5, 14, form.getZsbh()==null?"":form.getZsbh(), wcf);//证书编号

            ExcelUtil.modiStrCell2(wsOne, 3, 23, form.getStartYear()==null?"":form.getStartYear(), wcf3);//开始-年
            ExcelUtil.modiStrCell2(wsOne, 5, 23, form.getStartMonth()==null?"":form.getStartMonth(), wcf3);//开始-月
            ExcelUtil.modiStrCell2(wsOne, 7, 23, form.getStartDay()==null?"":form.getStartDay(), wcf3);//开始-日
            ExcelUtil.modiStrCell2(wsOne, 10, 23, form.getEndYear()==null?"":form.getEndYear(), wcf3);//结束-年
            ExcelUtil.modiStrCell2(wsOne, 12, 23, form.getEndMonth()==null?"":form.getEndMonth(), wcf3);//结束-月
            ExcelUtil.modiStrCell2(wsOne, 14, 23, form.getEndDay()==null?"":form.getEndDay(), wcf3);//结束-日
            ExcelUtil.modiStrCell2(wsOne, 18, 23, form.getFzYear()==null?"":form.getFzYear(), wcf4);//发证-年
            ExcelUtil.modiStrCell2(wsOne, 20, 23, form.getFzMonth()==null?"":form.getFzMonth(), wcf3);//发证-月
            ExcelUtil.modiStrCell2(wsOne, 22, 23, form.getFzDay()==null?"":form.getFzDay(), wcf3);//发证-日
            ExcelUtil.modiStrCell2(wsOne, 21, 24, form.getJdTel()==null?"":form.getJdTel(), wcf2);//监督电话

           // wcf.setLocked(false);
            //wcf1.setLocked(false);
            //wcf2.setLocked(false);
                 
        } catch (Exception e) {
            e.printStackTrace();
            log.error("写入正式数据出现错误"+e);
        } 
        
    }
    /**
     * 生成后面的文件路径加上年月
     * @param realpath 定义的目录
     * @param fileNamesuffix 文件后缀名
     * @return
     */
    private String buildPath(String realpath,String fileName){
      
        String dateDir = GetFileName.getDirector();
        return realpath+"/"+dateDir+"/"+fileName;
        
    }
    

	/**
	 * 获取收件通知书里面的材料列表
	 * @param form
	 * @return
	 */
	public ShljYsForm getSjtzsField(ShljYsForm form){
		form.setSerialNo2(form.getSerialNo());
		form.setPassword2(form.getPassword());
		Long id = form.getId();
		YwComSjcllistForm sjclListForm=new YwComSjcllistForm();
		sjclListForm.setAjid(id);
		sjclListForm.setTplcode("shljys");
        List<YwComSjcllistForm> sjclList = ywComSjcllistService.getList(sjclListForm);
        YwComSjclForm fkForm = new YwComSjclForm();
//        fkForm.setFkid(form.getQyid());
        fkForm.setAjid(id);
        fkForm.setTplcode("shljys");
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        for (YwComSjcllistForm listForm : sjclList) {
            boolean hasExists = false;
            for (YwComSjclForm tplForm : sjclTpl) {
                if (tplForm.getTplid() != null && listForm.getId().longValue() == tplForm.getTplid().longValue()) {
                    if (StringUtils.isNotBlank(tplForm.getFilepath())) {
                        listForm.setState("1");
                        listForm.setHasfile("1");
                        listForm.setFilename(tplForm.getFilename());
                        listForm.setFilepath(tplForm.getFilepath());
                        listForm.setFiletype(tplForm.getFiletype());
                    } else {
                        listForm.setState("0");
                        listForm.setHasfile("0");
                    }
                    if (tplForm.getYjs() != null) {
                        listForm.setYjs(Long.valueOf(tplForm.getYjs()));
                    }
                    if (tplForm.getFyjs() != null) {
                        listForm.setFyjs(Long.valueOf(tplForm.getFyjs()));
                    }
                    listForm.setClid(tplForm.getId());
                    if ("1".equals(listForm.getSfdz())) {
                        listForm.setDisabled("1");
                    } else {
                        listForm.setDisabled("0");
                        if (tplForm.getSfdz() == null) {
                            listForm.setSfdz("0");
                        } else {
                            listForm.setSfdz(tplForm.getSfdz());
                        }
                    }
                    hasExists = true;
                }
            }
            if (!hasExists) {
                listForm.setState("0");
                listForm.setHasfile("0");
                if ("1".equals(listForm.getSfdz())) {
                    listForm.setDisabled("1");
                } else {
                    listForm.setDisabled("0");
                }
            }
        }
        if(sjclList.size()>0)
		{
				YwComSjcllistForm sjclForm01=sjclList.get(0);
				form.setTitleRow101(sjclForm01.getTitle());
				form.setTitleRow102(sjclForm01.getTitle());
				form.setFyjsRow101(sjclForm01.getFyjs());
				form.setFyjsRow102(sjclForm01.getFyjs());
				form.setFsRow101(sjclForm01.getYjs()+sjclForm01.getFyjs());
				form.setFsRow102(sjclForm01.getYjs()+sjclForm01.getFyjs());
				if("0".equals(sjclForm01.getSfdz()))
				{
					form.setSfdzRow101("√");
					form.setSfdzRow102("√");
				}else{
					form.setSfdzRow101("√");
					form.setSfdzRow102("√");
				}
				
				YwComSjcllistForm sjclForm02=sjclList.get(1);
				form.setTitleRow201(sjclForm02.getTitle());
				form.setTitleRow202(sjclForm02.getTitle());
				form.setFyjsRow201(sjclForm02.getFyjs());
				form.setFyjsRow202(sjclForm02.getFyjs());
				form.setFsRow201(sjclForm02.getYjs()+sjclForm02.getFyjs());
				form.setFsRow202(sjclForm02.getYjs()+sjclForm02.getFyjs());
				if("0".equals(sjclForm02.getSfdz()))
				{
					form.setSfdzRow201("√");
					form.setSfdzRow202("√");
				}else{
					form.setSfdzRow201("√");
					form.setSfdzRow202("√");
				}
				
				
				YwComSjcllistForm sjclForm03=sjclList.get(2);
				form.setTitleRow301(sjclForm03.getTitle());
				form.setTitleRow302(sjclForm03.getTitle());
				form.setFyjsRow301(sjclForm03.getFyjs());
				form.setFyjsRow302(sjclForm03.getFyjs());
				form.setFsRow301(sjclForm03.getYjs()+sjclForm03.getFyjs());
				form.setFsRow302(sjclForm03.getYjs()+sjclForm03.getFyjs());
				if("0".equals(sjclForm03.getSfdz()))
				{
					form.setSfdzRow301("√");
					form.setSfdzRow302("√");
				}else{
					form.setSfdzRow301("√");
					form.setSfdzRow302("√");
				}
				
				YwComSjcllistForm sjclForm04=sjclList.get(3);
				form.setTitleRow401(sjclForm04.getTitle());
				form.setTitleRow402(sjclForm04.getTitle());
				form.setFyjsRow401(sjclForm04.getFyjs());
				form.setFyjsRow402(sjclForm04.getFyjs());
				form.setFsRow401(sjclForm04.getYjs()+sjclForm04.getFyjs());
				form.setFsRow402(sjclForm04.getYjs()+sjclForm04.getFyjs());
				if("0".equals(sjclForm04.getSfdz()))
				{
					form.setSfdzRow401("√");
					form.setSfdzRow402("√");
				}else{
					form.setSfdzRow401("√");
					form.setSfdzRow402("√");
				}
				
				YwComSjcllistForm sjclForm05=sjclList.get(4);
				form.setTitleRow501(sjclForm05.getTitle());
				form.setTitleRow502(sjclForm05.getTitle());
				form.setFyjsRow501(sjclForm05.getFyjs());
				form.setFyjsRow502(sjclForm05.getFyjs());
				form.setFsRow501(sjclForm05.getYjs()+sjclForm05.getFyjs());
				form.setFsRow502(sjclForm05.getYjs()+sjclForm05.getFyjs());
				
				if("0".equals(sjclForm05.getSfdz()))
				{
					form.setSfdzRow501("√");
					form.setSfdzRow502("√");
				}else{
					form.setSfdzRow501("√");
					form.setSfdzRow502("√");
				}
				
				YwComSjcllistForm sjclForm06=sjclList.get(5);
				form.setTitleRow601(sjclForm06.getTitle());
				form.setTitleRow602(sjclForm06.getTitle());
				form.setFyjsRow601(sjclForm06.getFyjs());
				form.setFyjsRow602(sjclForm06.getFyjs());
				form.setFsRow601(sjclForm06.getYjs()+sjclForm06.getFyjs());
				form.setFsRow602(sjclForm06.getYjs()+sjclForm06.getFyjs());
				if("0".equals(sjclForm06.getSfdz()))
				{
					form.setSfdzRow601("√");
					form.setSfdzRow602("√");
				}else{
					form.setSfdzRow601("√");
					form.setSfdzRow602("√");
				}
				YwComSjcllistForm sjclForm07=sjclList.get(6);
				form.setTitleRow701(sjclForm07.getTitle());
				form.setTitleRow702(sjclForm07.getTitle());
				form.setFyjsRow701(sjclForm07.getFyjs());
				form.setFyjsRow702(sjclForm07.getFyjs());
				form.setFsRow701(sjclForm07.getYjs()+sjclForm07.getFyjs());
				form.setFsRow702(sjclForm07.getYjs()+sjclForm07.getFyjs());
				if("0".equals(sjclForm07.getSfdz()))
				{
					form.setSfdzRow701("√");
					form.setSfdzRow702("√");
				}else{
					form.setSfdzRow701("√");
					form.setSfdzRow702("√");
				}
				
				
				YwComSjcllistForm sjclForm08=sjclList.get(7);
				form.setTitleRow801(sjclForm08.getTitle());
				form.setTitleRow802(sjclForm08.getTitle());
				form.setFyjsRow801(sjclForm08.getFyjs());
				form.setFyjsRow802(sjclForm08.getFyjs());
				form.setFsRow801(sjclForm08.getYjs()+sjclForm08.getFyjs());
				form.setFsRow802(sjclForm08.getYjs()+sjclForm08.getFyjs());
				
				if("0".equals(sjclForm08.getSfdz()))
				{
					form.setSfdzRow801("√");
					form.setSfdzRow802("√");
				}else{
					form.setSfdzRow801("√");
					form.setSfdzRow802("√");
				}
				
				YwComSjcllistForm sjclForm09=sjclList.get(8);
				form.setTitleRow901(sjclForm09.getTitle());
				form.setTitleRow902(sjclForm09.getTitle());
				form.setFyjsRow901(sjclForm09.getFyjs());
				form.setFyjsRow902(sjclForm09.getFyjs());
				form.setFsRow901(sjclForm09.getYjs()+sjclForm09.getFyjs());
				form.setFsRow902(sjclForm09.getYjs()+sjclForm09.getFyjs());
				
				if("0".equals(sjclForm09.getSfdz()))
				{
					form.setSfdzRow901("√");
					form.setSfdzRow902("√");
				}else{
					form.setSfdzRow901("√");
					form.setSfdzRow902("√");
				}
				
				YwComSjcllistForm sjclForm10=sjclList.get(9);
				form.setTitleRow1001(sjclForm10.getTitle());
				form.setTitleRow1002(sjclForm10.getTitle());
				form.setFyjsRow1001(sjclForm10.getFyjs());
				form.setFyjsRow1002(sjclForm10.getFyjs());
				form.setFsRow1001(sjclForm10.getYjs()+sjclForm10.getFyjs());
				form.setFsRow1002(sjclForm10.getYjs()+sjclForm10.getFyjs());
				
				if("0".equals(sjclForm10.getSfdz()))
				{
					form.setSfdzRow1001("√");
					form.setSfdzRow1002("√");
				}else{
					form.setSfdzRow1001("√");
					form.setSfdzRow1002("√");
				}
				
				YwComSjcllistForm sjclForm11=sjclList.get(10);
				form.setTitleRow1101(sjclForm11.getTitle());
				form.setTitleRow1102(sjclForm11.getTitle());
				form.setFyjsRow1101(sjclForm11.getFyjs());
				form.setFyjsRow1102(sjclForm11.getFyjs());
				form.setFsRow1101(sjclForm11.getYjs()+sjclForm11.getFyjs());
				form.setFsRow1102(sjclForm11.getYjs()+sjclForm11.getFyjs());
				
				if("0".equals(sjclForm11.getSfdz()))
				{
					form.setSfdzRow1101("√");
					form.setSfdzRow1102("√");
				}else{
					form.setSfdzRow1101("√");
					form.setSfdzRow1102("√");
				}
				
				
		}
        
        
		return form;
	}
    /**
     * 生成证书编号
     */
	public String createZsbh(Map map,Long id) {
		String tablename=(String) map.get("tablename");
		String zsname=(String) map.get("zsname");
		
		String sql= "select * from "+tablename+" where id="+id;
		List list=this.shljYsDao.getListById(sql);
		if(list!=null && list.size()>0){
			Map dataMap=(Map) list.get(0);
			String zsbh=(String) dataMap.get(zsname);
			String area=(String) map.get("xmqy");
			if(StringUtils.isBlank(zsbh)){
				//开始加入证书编号
				String zsnum=this.buildZsbh(tablename, area);
				String updatesql=" update "+tablename+" set fzh='"+zsnum+"' where id="+id;
				shljYsDao.updateDateBase(updatesql);
			}
		}
		
		return null;
	}

	public String buildZsbh(String tablename,String area){
		if(StringUtils.isBlank(area) || "null".equals(area)){
			area="00";//如果空就是00
		}
		String sql="select count(id) as countnum from "+tablename;
		List list=shljYsDao.queryForList(sql);
		Map map=(Map) list.get(0);
		String numstr=(String) map.get("countnum");
		if(StringUtils.isNotBlank(numstr)){
			Long num=Long.valueOf(numstr)+1;
			int k=4-String.valueOf(num).length();
			String prefix="";
			for(int i=1;i<=k;i++){
				prefix+="0";
			}
			return area+prefix+String.valueOf(num);
		}else{
			return area+"0001";
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.csshlj.service.IShljYsService#reSetForm(com.augurit.gzcgwsp.csshlj.form.ShljYsForm)
	 */
	public void reSetForm(ShljYsForm form) {
		this.csshljCzService.reSetForm(form);
	}
	
	//自动启动流程
	public void autoStartProc(ShljYsForm form,String templateCode){
		if(form != null && form.getId() != null){
			//测试需要保存一下
		//	this.save(form);
			
			//设置汇总字段
			form.setSqdw(form.getQymc());
			form.setSarq(form.getSlrq());
			
			//流程变量处理，默认将主业务对象作为form变量
			Map<String, Object> params = new HashMap<String, Object>();
			params.put(WfConstant.VAR_MASTER_ENTITY_ALIAS, form);
			
			//表示是自动启动流程,用来控制流程在"经办人员调查"环节是否启动人员分配事件
//			String areaCode = "4";
			String userCode = wfServiceEX.findBoPlaceCode(null, 111l);//111是【水域中心经办人】角色ID 写死
			System.out.println("cccccccccc = "+userCode);
			params.put("isAutoStartProc", "1");
			params.put("var_jbr",userCode);
			//获取该区的流程启动者
			String stratUser = wfServiceEX.getUser("syzx_ck", null);//syzx_ck【水域中心窗口】
			WfBusInstanceTaskForm task = wfService.wfBusStart(templateCode, form, stratUser, params);
			String executionId =  processEngine.getHistoryService().createHistoryTaskQuery().taskId(task.getTaskInstDbid().toString()).uniqueResult().getExecutionId();
            processEngine.getExecutionService().jumpToActivity(executionId, "xyzxjbr");//跳到【经办人现场勘察、初审】 环节     xyzxjbr是环节英文名称
		}
	}

	/*
	 * 查询待办
	 * @see com.augurit.gzcgwsp.csshlj.service.IShljYsService#getDbData(org.springside.modules.orm.Page, java.util.Map)
	 */
	public Page getDbData(Page page, Map map) {
		String sql=getSql(map);
		Page pg = PageUtils.newPageInstance(page);
		shljYsDao.findPage(pg, sql);
		List<ShljqsForm>  list = convertToList(pg.getResult());
		PageUtils.copy(pg, list, page);
		return page;
		//return sb.toString();
	}
	
	/**
	 * 构造sql
	 * @param map
	 * @return
	 */
	private String getSql(Map map){
		StringBuffer sb=new StringBuffer();
		String username=(String) map.get("username");
		String startdate=(String)map.get("startdate");
		String qyname=(String)map.get("qyname");
		String enddate=(String)map.get("enddate");
		if(StringUtils.isNotBlank(enddate)){
			String[] enddates=enddate.split("-");
			if(enddates.length==3){
				if(enddates[1].length()<2){//如果日期是2012-5-12这种格式
					enddate=enddates[0]+"-0"+enddates[1]+"-"+enddates[2];
				}
			}
		}
		if(StringUtils.isBlank(enddate)){//如果日期为空就为当前日期
			enddate=DateUtilsEX.dateFormate(new Date(),"yyyy-MM-dd");
			enddate=enddate.substring(0,10)+" 23:59:59";
		}else if(enddate.length()>=10){
			enddate=enddate.substring(0,10);
			enddate=enddate.substring(0,10)+" 23:59:59";
		}
		if(StringUtils.isBlank(startdate)){//如果日期为空显示3天前的数据
			startdate=DateUtilsEX.getDateByDay(new Date(),-3,"yyyy-MM-dd");
		}else if(startdate.length()>=10){
			startdate=startdate.substring(0,10);
		}
		sb.append("select ys from "+ShljYs.class.getName()+" as ys,");
		sb.append("Jbpm4HistProcinst as proc,WfBusInstance inst,Jbpm4HistTask as task ");
		sb.append(" where ys.id=inst.masterEntityKey and proc.procInstId = inst.procInstId");
		sb.append(" and task.procInstId = proc.procInstId and task.state='"+Task.STATE_OPEN+"'");
		sb.append(" and inst.masterEntity='ShljYs'");
		sb.append(" and task.assignee='"+username+"'"+" and task.activityName='jbrsj'");
		if(StringUtils.isNotBlank(qyname)){
			sb.append(" and ys.qymc like '%"+qyname+"%'");
		}
		if(StringUtils.isNotBlank(startdate)){
			sb.append(" and task.signTime>=to_date('"+startdate+"','yyyy-mm-dd')");
		}
		if(StringUtils.isNotBlank(enddate)){
			sb.append(" and task.signTime<=to_date('"+enddate+"','yyyy-mm-dd HH24:mi:ss')");
		}
		return sb.toString();
	}
	
		/**
		 * 转换form
		 * @param result
		 * @return
		 */
		private List<ShljqsForm> convertToList(List<ShljYs> result) {
			List<ShljqsForm> list=new ArrayList<ShljqsForm>();
		    if(result!=null && result.size()>0){
		    	ShljqsForm form=null;
		    	for(ShljYs objs:result){
		    		form=new ShljqsForm();
		    		String sqdw=(String)objs.getQymc();
		    		String frdb=(String)objs.getFrdb();
		    		String lxdh=(String)objs.getLxdh2();
		    		String startdate=objs.getJysjsstr();//发证开始时间
		    		String enddate=objs.getJysjestr();//发证结束时间
		    		String address=objs.getBgzs();//办公住所
		    		form.setAddress(address==null?"":address);
		    		form.setId(objs.getId());
		    		form.setCbyw("(水域)城市生活垃圾经营性清扫（捞）、收集服务行政许可审批");
		    		//获取车辆经营的号码
		    		String carNo=getCheckCar(objs.getId(),"shljys");
		    		form.setCheckcar(carNo);
		    		form.setJyscale(carNo);
		    		form.setSsAddr(address==null?"":address);
		    		form.setAddress(address==null?"":address);
		    		form.setFrdb(frdb==null?"":frdb);
		    		form.setSqdw(sqdw==null?"":sqdw);
		     		if(StringUtils.isBlank(startdate)){
		    			startdate=DateUtilsEX.getCurYear()+"年"+"  月"+"  日";
		    		}
		    		if(StringUtils.isBlank(enddate)){
		    			enddate=DateUtilsEX.getCurYear()+"年"+"  月"+"  日";
		    		}
		    		form.setValiddate(startdate+"~~"+enddate);
		    		form.setValidstarttime(startdate==null?"":startdate);
		    		form.setValidendtime(enddate==null?"":enddate);
		    		form.setCompanyphone(lxdh==null?"":lxdh);
		    		list.add(form);
		    	}
				
		    }
			return list;
		}
		/**
		 * 循环获取车牌号
		 * @param id
		 * @param tempcode
		 * @return
		 */
		private String getCheckCar(Long id, String tempcode) {
			List<YwHwYsclgj> list=ywHwYsclgjService.getCheckCar(id, tempcode);
			String carNo="";
			for(YwHwYsclgj entity:list){
				carNo+=entity.getXh()+"、";
			}
			if(StringUtils.isNotBlank(carNo)){
				carNo=carNo.substring(0, carNo.length()-1);
			}
			return carNo;
		}

		/*
		 *导出时查询待办的数据 (non-Javadoc)
		 * @see com.augurit.gzcgwsp.csshlj.service.IShljYsService#getDbDatas(java.util.Map)
		 */
		public List<Object[]> getDbDatas(Map map) {
			String sql=getSql(map);
			List<ShljYs> list=shljYsDao.find(sql);
			List<Object[]> datas=new ArrayList<Object[]>();
			for(ShljYs obj:list){
				Object[] objs=new Object[9];
				String sqdw=(String)obj.getQymc();
	    		String frdb=(String)obj.getFrdb();
	    		String lxdh=(String)obj.getLxdh2();
	    		String startdate=obj.getJysjsstr();//发证开始时间
	    		String enddate=obj.getJysjestr();//发证结束时间
	    		String address=obj.getBgzs();//办公住所
	    		//获取车辆经营的号码
	    		String carNo=getCheckCar(obj.getId(),"shljys");
	    		objs[0]=obj.getId();
	    		objs[1]=sqdw;
	    		objs[2]=frdb;
	    		objs[3]=lxdh;
	    		objs[4]=startdate;
	    		objs[5]=enddate;
	    		objs[6]=carNo;
	    		objs[7]=address;
	    		datas.add(objs);
			}
			return datas;
		}
		
		/*
		 * 新增(non-Javadoc)
		 * @see com.augurit.gzcgwsp.csshlj.service.IShljYsService#wfBusSave(javax.servlet.http.HttpServletRequest, com.augurit.gzcgwsp.csshlj.form.ShljYsForm, java.util.Map, java.lang.String)
		 */
		public WfBusInstanceTaskForm wfBusSave(HttpServletRequest request,
				ShljYsForm form, Map map, String username) {
			WfBusInstanceTaskForm task = wfSupportService.wfBusSave(request);
			if(task==null)
				return null;
			
				String entityKey = task.getMasterEntityKey();
				String ysl = (String) map.get("syshlj_ysl");
				String sqdw = (String) map.get("syshlj_qymc"); // 申请单位
				String acceptNo = updateAcceptNoAndSumary(entityKey, sqdw, ysl,username); // 更新入受理号以及汇总字段
				Long id = null;
				if (task != null && task.getMasterEntityKey() != null) {
					id = Long.valueOf(task.getMasterEntityKey());
				}
				form.setId(id);
				task.setMessage(acceptNo); //设置受理编号
				if (ysl.equals("1")) { // 更新预受理状态
					changeYslState(id, 2l,null);
				}
				form.setId(id);
				
				//构造map
				String qyid=(String) map.get("syshlj_qybh");//企业id
				String frdb=(String) map.get("syshlj_frdb");//法人代表
				String yyzzzch=(String) map.get("syshlj_yyzzzch");//营业注册号码
				String tel=(String) map.get("syshlj_lxdh");//联系电话
				String regmoney=(String) map.get("syshlj_zczb");//注册资金
				String fax=(String) map.get("syshlj_czdh");//传真
				String email=(String) map.get("syshlj_email");//电子邮箱
				String post=(String) map.get("syshlj_yzbm");//邮编
				String address=(String)map.get("syshlj_bgzs");//办公住所
				String zsbh=(String)map.get("syshlj_fzh");//证书编号
				map.put("qyid", qyid);
				map.put("tel",tel);
				map.put("sqdw",sqdw);
				map.put("frdb",frdb);
				map.put("yyzzzch", yyzzzch);
				map.put("regmoney", regmoney);
				map.put("fax", fax);
				map.put("email", email);
				map.put("post", post);
				map.put("address", address);
				if(zsbh!=null && zsbh.length()>0){
					map.put("zsbh",zsbh);
				}
				Long qybh=csshljCzService.saveEnterpriseInfo("shljys", map);
				if(qybh!=null){
					form.setQybh(String.valueOf(qybh));
					updateEntityByQyId(qybh,id);//设置企业编号
				}
				return task;
		}
		
		/**
		 * 设置企业id
		 * @param qybh
		 * @param id
		 */
		private void updateEntityByQyId(Long qybh, Long id) {
			String sql=" update SYSHLJ_V1 set QYBH="+qybh +" where id="+id ;
			this.shljYsDao.updateDateBase(sql);
		}
		/**
		 * 更新受理编号和汇总字段
		 * @param entityKey
		 * @return
		 */
		private String updateAcceptNoAndSumary(String entityKey, String sqdw,
				String ysl,String username) {
			String acceptNo = "";
			if (entityKey != null && entityKey.trim().length() > 0) {
				String sql = "SELECT ACCEPT_NO FROM SYSHLJ_V1 t WHERE t.ID = "
						+ entityKey;
				List list = smartDao.getResultList(sql);
				if (list != null) {
					String bh = (String) list.get(0);
					if (bh == null || bh.trim().length() <= 0) {
//						OaDocCodeForm doccodeForm = docCodeService.get(10L);
//						acceptNo = CommonUtils.convertSeqToAcceptNo(doccodeForm
//								.getSeq());
//						docCodeService.updateForAddSeq(doccodeForm.getId());

			        	//生成政务中心统一查询编号 开始
						Map<String,String> serialNoAndPasswordMap = null;
			        	serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("0");			        	
			        	String serialNo = serialNoAndPasswordMap.get("serialNo");
			        	String password = serialNoAndPasswordMap.get("password");
			        	//生成政务中心统一查询编号 结束
			        	acceptNo = serialNo;
						String updateSql = "UPDATE SYSHLJ_V1 t SET t.ACCEPT_NO = '"
								+ serialNo
								+ "',t.SERIAL_NO='"
								+ serialNo
								+"',t.PASSWORD='"
								+ password
								+ "',t.SJR='"
								+ username
								+ "' WHERE t.ID = " + entityKey;
						smartDao.executeSql(updateSql);
						// =====================更新完受理编号========

						// =====================更新汇总字段===========
						WfBusInstance entity = wfBusInstanceService
								.getByMasterEntityAndKey("ShljYs", entityKey);
						entity.setBusMemo1(sqdw);
						entity.setBusMemo3(serialNo);
						entity.setBusMemo4("(水域)城市生活垃圾经营性清扫（捞）、收集服务行政许可审批");
						entity.setBusDate2(new Date());
						WfBusInstanceForm form = WfBusInstanceConvertor
								.convertVoToForm(entity);
						wfBusInstanceService.save(form);

					} else if ((bh != null && bh.trim().length() > 0) && ysl.equals("1")) { // 预受理，汇总字段
						
						//设置收件人  开始
						String updateSql = "UPDATE SYSHLJ_V1 t SET t.SJR='"
							+ username
							+ "' WHERE t.ID = " + entityKey;
						smartDao.executeSql(updateSql);
						//设置收件人  结束
						acceptNo = bh;
						WfBusInstance entity = wfBusInstanceService
								.getByMasterEntityAndKey("ShljYs", entityKey);
						entity.setBusMemo1(sqdw);
						entity.setBusMemo3(acceptNo);
						entity.setBusMemo4("(水域)城市生活垃圾经营性清扫（捞）、收集服务行政许可审批");
						entity.setBusDate2(new Date());
						WfBusInstanceForm form = WfBusInstanceConvertor
								.convertVoToForm(entity);
						wfBusInstanceService.save(form);

					} else {
						acceptNo = bh;
					}
				}
			}
			return acceptNo;
		}
		
		/*
		 * 获取已结信息
		 * @see com.augurit.gzcgwsp.csshlj.service.IShljYsService#getYjInfo(com.augurit.gzcgwsp.csshlj.form.ShljYsBaForm)
		 */
		public List<ShljYsBaForm>  getYjInfo(ShljYsBaForm form,String type) {
			StringBuffer sb=new StringBuffer();
			if("sy".equals(type)){//水域
				sb.append("select distinct inst.wfBusInstanceId,temp.templateName,ys.slrq  from ShljYs as ys,");
				sb.append("Jbpm4HistProcinst as proc,WfBusInstance inst,Jbpm4HistTask as task, WfBusTemplate temp");
				sb.append(" where inst.masterEntityKey="+form.getSyysid()+" and inst.masterEntityKey=ys.id and proc.procInstId = inst.procInstId");
				
				sb.append(" and task.procInstId = proc.procInstId and task.state='"+Task.STATE_COMPLETED+"' and proc.state='ended' ");		
				sb.append(" and  inst.templateId = temp.id and inst.masterEntity='ShljYs' order by ys.slrq");
			}else{//陆域
				sb.append("select distinct inst.wfBusInstanceId,temp.templateName,ly.tbrq from YwHwShljqs as ly, ");
				sb.append("Jbpm4HistProcinst as proc,WfBusInstance inst,Jbpm4HistTask as task, WfBusTemplate temp");
				sb.append(" where inst.masterEntityKey="+form.getSyysid()+" and inst.masterEntityKey=ly.id and proc.procInstId = inst.procInstId");
				sb.append(" and task.procInstId = proc.procInstId and task.state='"+Task.STATE_COMPLETED+"' and proc.state='ended'");
				sb.append(" and  inst.templateId = temp.id and inst.masterEntity='YwHwShljqs' order by ly.tbrq");
			}
			List<ShljYsBaForm> forms=new ArrayList<ShljYsBaForm>();
			List<Object[]> list=this.shljYsDao.find(sb.toString());
			if(list != null && list.size() > 0){
				ShljYsBaForm ybform=new ShljYsBaForm();
				ybform.setInsId((Long)list.get(0)[0]);
				ybform.setTempName((String)list.get(0)[1]);
				ybform.setSlrq((Date)list.get(0)[2]);
				forms.add(ybform);
				List<Object[]> baList=this.getBaInfo(type);
				for(Object[] obj:baList){
					ShljYsBaForm baform=new ShljYsBaForm();
					baform.setInsId((Long)obj[0]);
					baform.setTempName((String)obj[1]);
					baform.setSlrq((Date)obj[2]);
					forms.add(baform);
				}
			}			
			return forms;
		}
		
		/**
		 * 查询备案的数据
		 * @return
		 */
		public List<Object[]> getBaInfo(String type){
			StringBuffer sb=new StringBuffer();
			if("sy".equals(type)){//水域
				sb.append("select distinct inst.wfBusInstanceId,temp.templateName,ba.slrq  from ShljYsBa ba,");
				sb.append("Jbpm4HistProcinst as proc,WfBusInstance inst,Jbpm4HistTask as task, WfBusTemplate temp");
				sb.append(" where inst.masterEntityKey=ba.id and proc.procInstId = inst.procInstId");
				sb.append(" and task.procInstId = proc.procInstId and task.state='"+Task.STATE_COMPLETED+"' and proc.state='ended'");
				sb.append(" and  inst.templateId = temp.id and inst.masterEntity='ShljYsBa' order by ba.slrq");
			}else{//陆域
				sb.append("select distinct inst.wfBusInstanceId,temp.templateName,ba.sqrq  from ShljqsBa ba,");
				sb.append("Jbpm4HistProcinst as proc,WfBusInstance inst,Jbpm4HistTask as task, WfBusTemplate temp");
				sb.append(" where inst.masterEntityKey=ba.id and proc.procInstId = inst.procInstId");
				sb.append(" and task.procInstId = proc.procInstId and task.state='"+Task.STATE_COMPLETED+"' and proc.state='ended'");
				sb.append(" and  inst.templateId = temp.id and inst.masterEntity='ShljqsBa' order by ba.sqrq");
			}
		
			return this.shljYsDao.find(sb.toString());
		}
		
		/**
	     * 保存政务中心的统一查询编号和密码
	     * @param id
	     * @param serialNo
	     * @param password
	     */
	    public void saveSerialNoAndPassword(Long id,String serialNo,String password) {
	    	ShljYs entity = shljYsDao.get(id);
	    	entity.setSerialNo(serialNo);
	    	entity.setPassword(password);
	    	shljYsDao.save(entity);
	    }

	    /*
	     * (non-Javadoc)
	     * @see com.augurit.gzcgwsp.csshlj.service.IShljYsService#saveOtherInf(java.util.Map)
	     */
		@Override
		public void saveOtherInf(Map map) {
			String templateCode=(String) map.get("templateCode");
			String id=(String) map.get("id");
			String fzsj=(String) map.get("fzsj");
			String fzh=(String) map.get("fzh");
			String kssj=(String) map.get("kssj");
			String jssj=(String) map.get("jssj");
			String sql="";
			if("shljqssj".equals(templateCode)){//陆域HW_SHLJQS_V1
				   sql= "Update HW_SHLJQS_V1 set fzsjstr ='"
        			  + fzsj+"',fzh='"+fzh+"',jysjsstr='"+kssj+"'"+",jysjestr='"+jssj+"'" 
        			  + " where id = " + Long.parseLong(id);
			}else if("shljys".equals(templateCode)){
				  sql= "Update HW_SHLJQS_V1 set fzsjstr ='"
       			  + fzsj+",fzh='"+fzh+"',jysjsstr='"+kssj+"'"+",jysjestr='"+jssj+"'" 
       			  + " where id = " + Long.parseLong(id);
			}else if("shljcz".equals(templateCode)){//处置
				  sql= "Update HW_SHLJQS_V1 set fzsjstr ='"
       			  + fzsj+",fzh='"+fzh+"',jysjsstr='"+kssj+"'"+",jysjestr='"+jssj+"'" 
       			  + " where id = " + Long.parseLong(id);
			}
			this.shljYsDao.updateDateBase(sql);
		}
		
		public String wfBusYslTuih(ShljYsForm form,LoginUserForm loginUser) throws Exception {
	    	String  loginUserName = loginUser.getUser().getUserName();
	    	WfBusInstanceTaskForm taskForm = new WfBusInstanceTaskForm();
			taskForm.setHandleComments("资料不全，不予受理。");
			//把审批状态数据保存到ywWebserviceInfo ，交换给方欣
			ywWebserviceInfoService.saveYwWebserviceInfo(form.getSqrq(), form.getAcceptNo(), form.getCbyw(), form.getJbrxm(), form.getSjr(), form.getSqdw(), 
					form.getBusinessCode(), form.getApprovalNo(), loginUserName, loginUser.getOrg().getOrgName(), "ysl", new Date(), false, "预受理", taskForm);
//			System.out.println("form.getYslThyy() = "+java.net.URLDecoder.decode(form.getYslThyy(),"UTF-8"));
			this.changeYslState(form.getId(), 0L,taskForm.getHandleComments());
			
			//（网办）交换到市民网页
			Map map=exchangeStateService.buildMap(loginUser, null, form.getId().toString(), form.getTemplateCode(), false);
			String durationExpression = wfServiceEX.getDurationExpressionByTemplateCode(form.getTemplateCode());
			ywWebserviceForWbService.setValueAndSaveYwWebserviceForWb(loginUserName,form.getTemplateCode(),
					form.getId().toString(),durationExpression,"001",taskForm.getHandleComments(), map);
	    	return null;
	    }
}
